﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DevExpress.XtraGrid.Views.Grid;
using ExcelCOM = Microsoft.Office.Interop.Excel;
namespace BanHangCshap
{
    static class ExcelFile
    {
        public static OleDbConnection oledbconn = null;
        public static Boolean connectExcelFile(string filename)
        {
            string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filename;
            oledbconn = new OleDbConnection(strconn);
            try
            {
                oledbconn.Open();
                return true;
            }
            catch (Exception)
            {

                oledbconn = null;
            }
            return false;
        }
        public static DataTable getExcellSheetNames()
        {
            System.Data.DataTable dt = null;
            dt = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            return dt;
        }
        public static DataTable getContentExcelFile( string sheetname)
        {
            DataTable dt = new DataTable();
            string sql = "select * from [" + sheetname + "]";
            try
            {
                OleDbCommand olecmd = new OleDbCommand(sql, oledbconn);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = olecmd;
                adapter.Fill(dt);
            }
            catch (Exception)
            {
                //adapter = null;
               
            }
            
            return dt;

        }
        //public static String[] GetExcelSheetNames()
        //{

        //    System.Data.DataTable dt = null;

        //    try
        //    {


        //        // Get the data table containing the schema

        //        dt = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        //        if (dt == null)
        //        {
        //            return null;
        //        }

        //        String[] excelSheets = new String[dt.Rows.Count];
        //        int i = 0;

        //        // Add the sheet name to the string array.
        //        foreach (DataRow row in dt.Rows)
        //        {
        //            string strSheetTableName = row["TABLE_NAME"].ToString();
        //            excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
        //            i++;
        //        }


        //        return excelSheets;
        //    }
        //    catch (Exception)
        //    {
        //        return null;
        //    }
        //    finally
        //    {
        //        // Clean up.

        //        if (dt != null)
        //        {
        //            dt.Dispose();
        //            dt = null;
        //        }
        //    }
        //}
        //public static DataTable ReadExcelContents(string fileName, string sheetname)
        //{
        //    try
        //    {
        //        OleDbConnection connection = new OleDbConnection();

        //        connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName); //Excel 97-2003, .xls
        //        //string excelQuery = @"Select [Day],[Outlook],[temp],[Humidity],[Wind], [PlaySport]
        //        //   FROM [Sheet1$]";

        //        string excelQuery = @"Select * FROM [" + sheetname + "]";
        //        connection.Open();
        //        OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
        //        OleDbDataAdapter adapter = new OleDbDataAdapter();
        //        adapter.SelectCommand = cmd;
        //        DataSet ds = new DataSet();
        //        adapter.Fill(ds);
        //        DataTable dt = ds.Tables[0];


        //        //dataGridView1.DataSource = dt.DefaultView;
        //        connection.Close();
        //        return dt;
        //    }
        //    catch (Exception )
        //    {

        //        //MessageBox.Show("Không thể đọc file này do lỗi: " + ex.Message, "Đọc file excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
        //        return null;
        //    }
        //}
        //public static void exportExcel(string sheetName, string titleName, int sizeTitleName, GridView grv, string fileName)
        //{
        //    ExcelCOM.Application exApp = new ExcelCOM.Application();
        //    ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
        //    ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];

        //    exSheet.Name = sheetName;

        //    exSheet.Cells[2, 5] = titleName;
        //    ExcelCOM.Range rgn;
        //    rgn = exSheet.get_Range(exSheet.Cells[2, 5], exSheet.Cells[2, 5]);
        //    rgn.Font.Size = sizeTitleName;

        //    //exSheet.Cells[5, 3] = "Mã Học Phần";
        //    //exSheet.Cells[5, 4] = "Mã Nhóm Học Phần";
        //    //exSheet.Cells[5, 5] = "Tên Học Phần";
        //    //exSheet.Cells[5, 6] = "Điểm Thi";
        //    //exSheet.Cells[5, 7] = "Điểm Quá Trình";

        //    //exSheet.Cells[5, 8] = "Điểm Xếp Loại";

        //    //exSheet.Cells[4, 3] = "Mã SV : " + txtMaSV.Text + "";

        //    int j = grv.RowCount;

        //    int i = 0; j = 0;
        //    for (i = 0; i < grv.RowCount - 1; i++)
        //    {
        //        //MessageBox.Show(grv.Columns.Count.ToString());
        //        for (j = 0; j < grv.Columns.Count - 2; j++)
        //        {
        //            DataRow row = grv.GetDataRow(i);
        //            exSheet.Cells[i + 6, j + 3] = row[j].ToString();
        //            exSheet.Columns.AutoFit();

        //        }
        //    }
        //    exApp.Visible = true;
        //}

        //public static DataTable GetSheetData(string excelFile, int sheetindex)
        //{
        //    string sheetname = GetExcelSheetNames(excelFile)[sheetindex];
        //    string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
        //    string srcQuery = "Select * from [" + sheetname + "]";
        //    OleDbConnection srcConn = new OleDbConnection(srcConnString);
        //    srcConn.Open();
        //    OleDbCommand objCmdSelect = new OleDbCommand(srcQuery, srcConn);
        //    DataSet ds = new DataSet();
        //    OleDbDataAdapter da = new OleDbDataAdapter(objCmdSelect);
        //    da.Fill(ds, sheetname);
        //    srcConn.Close();
        //    return ds.Tables[0];
        //}


        //public static string[] GetExcelSheetNames(string excelFile)
        //{
        //    OleDbConnection objConn = null;
        //    System.Data.DataTable dt = null;
        //    try
        //    {
        //        // Connection String. Change the excel file to the file you 
        //        // will search. 
        //        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        //                "Data Source='" + excelFile + "';Extended Properties=Excel 8.0;";
        //        // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); 
        //        // Mở kết nối đến CSDL 
        //        objConn.Open();
        //        // Lấy về dữ liệu 
        //        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        //        if (dt == null)
        //        {
        //            return null;
        //        }
        //        String[] excelSheets = new String[dt.Rows.Count];
        //        int i = 0;
        //        // Add the sheet name to the string array. 
        //        foreach (DataRow row in dt.Rows)
        //        {
        //            excelSheets[i] = row["TABLE_NAME"].ToString();
        //            i++;
        //        }
        //        return excelSheets;
        //    }
        //    catch (Exception)
        //    {
        //        return null;
        //    }
        //    finally
        //    {
        //        // Clean up. 
        //        if (objConn != null)
        //        {
        //            objConn.Close();
        //            objConn.Dispose();
        //        }
        //        if (dt != null)
        //        {
        //            dt.Dispose();
        //        }
        //    }
        //}
    }
}
